﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Odbc;
using System.Collections;
using Clientes = LI4SilverWCF.bus.Clientes;


namespace LI4SilverWCF.data
{
    class DBClientes
    {
        OdbcConnection con;
        public DBClientes(OdbcConnection c)
        {
            con = c;
        }

        public Hashtable obterListaClientes()
        {
            Hashtable lista = new Hashtable();
            string query = "SELECT COD_CLIENTE, USERNAME FROM CLIENTE WHERE ESTADO = 'True'";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();

            try
            {
                while (reader.Read())
                {
                    lista.Add(reader.GetInt32(0), reader.GetString(1));
                }

            }
            catch (Exception e)
            {
            }
            con.Close();
            return lista;
        }

        public int calcularNumeroUtilizadoresBD()
        {
            string query = "SELECT COUNT(COD_CLIENTE) FROM CLIENTE WHERE ESTADO = 'True'";
            int num_clientes = 0;
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    num_clientes = reader.GetInt32(0);
                }
            }
            catch (Exception e)
            {
            }
            con.Close();

            return num_clientes;
        }

        public ArrayList obterListaUtilMaisClass(int numero)
        {
            ArrayList utils = new ArrayList();
            ArrayList cod_utils = new ArrayList();
            string query = "SELECT COD_CLIENTE, COUNT(COD_CLIENTE) AS ATRIB FROM VOTO_CLIENTE_FILME GROUP BY COD_CLIENTE ORDER BY ATRIB DESC";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read() && numero > 0)
                {
                    cod_utils.Add(reader.GetInt32(0));
                    numero--;
                }
            }
            catch (Exception e)
            {
            }
            con.Close();

            query = "SELECT USERNAME FROM CLIENTE WHERE COD_CLIENTE =";
            IEnumerator en = cod_utils.GetEnumerator();
            while (en.MoveNext())
            {
                command = new OdbcCommand(query + en.Current, con);
                con.Open();
                reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        utils.Add(reader.GetString(0));
                    }
                }
                catch (Exception e)
                {
                }
                con.Close();
            }

            return utils;
        }

        public ArrayList obterListaUtilMaisPontos(int numero)
        {
            ArrayList utils = new ArrayList();
            string query = "SELECT USERNAME, PONTOS_TOTAIS FROM CLIENTE ORDER BY PONTOS_TOTAIS DESC";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read() && numero > 0)
                {
                    utils.Add(reader.GetString(0));
                    numero--;
                }
            }
            catch (Exception e)
            {
            }
            con.Close();

            return utils;
        }

        public ArrayList obterListaUtilFreq(int numero)
        {
            ArrayList utils = new ArrayList();
            string query = "SELECT USERNAME, COUNT(USERNAME) AS VISITAS FROM CLIENTE INNER JOIN LOGFILE ON CLIENTE.COD_CLIENTE = LOGFILE.COD_CLIENTE WHERE DESCRICAO = 'Autenticacao Efectuada' AND ESTADO = 'True' GROUP BY USERNAME ORDER BY VISITAS DESC";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read() && numero > 0)
                {
                    utils.Add(reader.GetString(0));
                    numero--;
                }
            }
            catch (Exception e)
            {
            }
            con.Close();

            return utils;
        }

        public bool verificarUsername(String username)
        {
            string query = "SELECT COD_CLIENTE FROM CLIENTE WHERE USERNAME = '" + username + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            bool existe = reader.HasRows;
            con.Close();
            return existe;
        }

        public bool verificarEmailAmigo(String email)
        {
            string query = "SELECT COD_CLIENTE FROM CLIENTE WHERE MAIL = '" + email + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            bool existe = reader.HasRows;
            con.Close();
            return existe;
        }

        public bool verificarEmail(String email)
        {
            string query = "SELECT COD_CLIENTE FROM CLIENTE WHERE MAIL = '" + email + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            bool existe = reader.HasRows;
            con.Close();
            return existe;
        }

        public void adicionarPontos(String email)
        {
            string query = "UPDATE CLIENTE SET PONTOS_TOTAIS = (PONTOS_TOTAIS + 1000) WHERE MAIL = '" + email + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO ADICIONAR PONTOS AMIGO: " + e);
            }
            con.Close();

            query = "INSERT INTO LOGFILE (COD_CLIENTE,DATA,DESCRICAO) VALUES ((SELECT COD_CLIENTE FROM CLIENTE WHERE MAIL = '" + email + "'), CURRENT_TIMESTAMP, 'Pontos adicionados por convite de amigo')";
            command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO ACTUALIZAR LOGFILE: " + e);
            }
            con.Close();

        }

        public void registarDadosCliente(String username, String local, String password, String nome, DateTime nasc, String rua, String cod_postal, String bi, String nif, String mail, double latitude, double longitude, String sexo, ArrayList telefones)
        {
            string query;
            int cod_local = 0;
            query = "SELECT COD_LOCAL FROM LOCALIDADE WHERE LOCALIDADE = '" + local + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                    cod_local = reader.GetInt32(0);
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO PROCURAR CODIGO LOCALIDADE:" + e);
            }
            con.Close();

            query = "INSERT INTO CLIENTE (COD_LOCAL, USERNAME, PASSWORD, NOME, DATA_NASC, RUA, COD_POST, BI, NIF, MAIL, PONTOS_TOTAIS, PONTOS_GASTOS, ESTADO, LATITUDE, LONGITUDE, SEXO) VALUES (" + cod_local + ",'" + username + "', '" + password + "', '" + nome + "', CONVERT(DATETIME,'" + nasc.ToString("dd-MM-yyyy") + "',103), '" + rua + "', '" + cod_local + "', " + bi + ", " + nif + ", '" + mail + "', 0, 0, 'True', " + latitude.ToString().Replace(",", ".") + ", " + longitude.ToString().Replace(",", ".") + ", '" + sexo + "')";
            command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO INSERIR CLIENTE: " + e);
            }
            con.Close();

            int cod_cliente = 0;
            query = "SELECT COD_CLIENTE FROM CLIENTE WHERE USERNAME = '" + username + "'";
            command = new OdbcCommand(query, con);
            con.Open();
            reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                    cod_cliente = reader.GetInt32(0);
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO PROCURAR CODIGO CLIENTE:" + e);
            }
            con.Close();

            IEnumerator en = telefones.GetEnumerator();
            while (en.MoveNext())
            {
                query = "INSERT INTO TELEFONE (NUM_TEL,COD_CLIENTE) VALUES (" + en.Current + ", " + cod_cliente + ")";
                command = new OdbcCommand(query, con);
                con.Open();
                try
                {
                    command.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine("ERRO AO INSERIR TELEFONES CLIENTE: " + e);
                }
                con.Close();
            }

            query = "INSERT INTO LOGFILE (COD_CLIENTE,DATA,DESCRICAO) VALUES (" + cod_cliente + ", CURRENT_TIMESTAMP, 'Novo cliente registado')";
            command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO ACTUALIZAR LOGFILE: " + e);
            }
            con.Close();
        }

        public bool verificarUsernameEmail(String username, String email)
        {
            string query = "SELECT COD_CLIENTE FROM CLIENTE WHERE USERNAME = '" + username + "' AND MAIL = '" + email + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            bool existe = reader.HasRows;
            con.Close();
            return existe;
        }

        public void actualizarPassword(String username, String password)
        {
            string query = "UPDATE CLIENTE SET PASSWORD = '" + password + "' WHERE USERNAME = '" + username + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO ALERTAR PASSWORD: " + e);
            }
            con.Close();

            query = "INSERT INTO LOGFILE (COD_CLIENTE,DATA,DESCRICAO) VALUES ((SELECT COD_CLIENTE FROM CLIENTE WHERE USERNAME = '" + username + "'), CURRENT_TIMESTAMP, 'Password actualizada por esquecimento do cliente')";
            command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO ACTUALIZAR LOGFILE: " + e);
            }
            con.Close();

        }

        public bool verificarPassword(String username, String password)
        {
            string query = "SELECT COD_CLIENTE FROM CLIENTE WHERE USERNAME = '" + username + "' AND PASSWORD = '" + password + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            int cod_cliente = 0;
            bool existe = reader.HasRows;
            try
            {
                while (reader.Read())
                    cod_cliente = reader.GetInt32(0);
            }
            catch (Exception e)
            {
            }

            con.Close();

            return existe;
        }

        public bool verificarUsernamePassword(String username, String password)
        {
            string query = "SELECT COD_CLIENTE FROM CLIENTE WHERE USERNAME = '" + username + "' AND PASSWORD = '" + password + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            int cod_cliente = 0;
            bool existe = reader.HasRows;
            try
            {
                while (reader.Read())
                    cod_cliente = reader.GetInt32(0);
            }
            catch (Exception e)
            {
            }

            con.Close();

            if (existe)
            {
                query = "INSERT INTO LOGFILE (COD_CLIENTE , DATA, DESCRICAO) VALUES (" + cod_cliente + ", CURRENT_TIMESTAMP, 'Autenticacao Efectuada')";
                command = new OdbcCommand(query, con);
                con.Open();
                try
                {
                    command.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine("ERRO AO PREENCHER LOGFILE: " + e);
                }
                con.Close();
            }

            return existe;
        }

        public void desactivarConta(String username)
        {
            string query = "UPDATE CLIENTE SET ESTADO = 'False' WHERE USERNAME = '" + username + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO DESACTIVAR CONTA: " + e);
            }
            con.Close();

            query = "INSERT INTO LOGFILE (COD_CLIENTE,DATA,DESCRICAO) VALUES ((SELECT COD_CLIENTE FROM CLIENTE WHERE USERNAME = '" + username + "'), CURRENT_TIMESTAMP, 'Conta desactivada')";
            command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO ACTUALIZAR LOGFILE: " + e);
            }
            con.Close();
        }

        public void obterDadosCliente(String username, Clientes u)
        {
            string query = "SELECT COD_CLIENTE, LOCALIDADE, NOME, DATA_NASC, RUA, COD_POST, BI, NIF, MAIL, PONTOS_TOTAIS, PONTOS_GASTOS, ESTADO, LATITUDE, LONGITUDE, SEXO "
                          + "FROM CLIENTE INNER JOIN LOCALIDADE ON CLIENTE.COD_LOCAL = LOCALIDADE.COD_LOCAL "
                          + "WHERE USERNAME = '" + username + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            int cod_cliente = 0;
            try
            {
                while (reader.Read())
                {
                    if (!reader.IsDBNull(0))
                    {
                        u.codigoCliente = (int)reader.GetInt32(0);
                        cod_cliente = reader.GetInt32(0);
                    }
                    if (!reader.IsDBNull(1)) u.localidade = reader.GetString(1);
                    if (!reader.IsDBNull(2)) u.nome = reader.GetString(2);
                    if (!reader.IsDBNull(3)) u.dataNascimento = reader.GetDateTime(3);
                    if (!reader.IsDBNull(4)) u.rua = reader.GetString(4);
                    if (!reader.IsDBNull(5)) u.codigoPostal = reader.GetString(5);
                    //
                    if (!reader.IsDBNull(6)) u.bi = reader.GetString(6);
                    if (!reader.IsDBNull(7)) u.nif = reader.GetString(7);
                    //
                    if (!reader.IsDBNull(8)) u.mail = reader.GetString(8);
                    if (!reader.IsDBNull(9)) u.pontosTotais = (int)reader.GetInt32(9);
                    if (!reader.IsDBNull(10)) u.pontosGastos = (int)reader.GetInt32(10);
                    if (!reader.IsDBNull(11)) u.estadoConta = (bool)reader.GetBoolean(11);
                    if (!reader.IsDBNull(12)) u.latitude = (double)reader.GetDouble(12);
                    if (!reader.IsDBNull(13)) u.longitude = (double)reader.GetDouble(13);
                    if (!reader.IsDBNull(14)) u.sexo = reader.GetString(14);
                }

            }
            catch (Exception e)
            {
            }
            con.Close();
            ArrayList tlf = new ArrayList();
            query = "SELECT NUM_TEL FROM TELEFONE WHERE COD_CLIENTE = " + cod_cliente;
            command = new OdbcCommand(query, con);
            con.Open();
            reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    tlf.Add(reader.GetInt32(0));
                }
            }
            catch (Exception e)
            {
            }
            con.Close();
            u.telefones = tlf;
        }

        public void actualizarDadosCliente(String username, String rua, String cod_postal, String mail, double latitude, double longitude, ArrayList telefones)
        {
            string query = "UPDATE CLIENTE SET RUA = '" + rua + "', COD_POST = '" + cod_postal + "', MAIL = '" + mail + "', LATITUDE = " + latitude.ToString().Replace(",", ".") + ", LONGITUDE = " + longitude.ToString().Replace(",", ".") + " WHERE USERNAME = '" + username + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO ACTUALIZAR DADOS CLIENTE: " + e);
            }
            con.Close();

            int cod_cliente = 0;
            query = "SELECT COD_CLIENTE FROM CLIENTE WHERE USERNAME = '" + username + "'";
            command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                    cod_cliente = reader.GetInt32(0);
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO PROCURAR CODIGO CLIENTE:" + e);
            }
            con.Close();

            query = "DELETE FROM TELEFONE WHERE COD_CLIENTE = " + cod_cliente;
            command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO ACTUALIZAR TELEFONES DO CLIENTE: " + e);
            }
            con.Close();


            IEnumerator en = telefones.GetEnumerator();
            while (en.MoveNext())
            {
                query = "INSERT INTO TELEFONE (NUM_TEL,COD_CLIENTE) VALUES (" + en.Current + ", " + cod_cliente + ")";
                command = new OdbcCommand(query, con);
                con.Open();
                try
                {
                    command.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Console.WriteLine("ERRO AO ACTUALIZAR TELEFONES DO CLIENTE: " + e);
                }
                con.Close();
            }

            query = "INSERT INTO LOGFILE (COD_CLIENTE,DATA,DESCRICAO) VALUES ((SELECT COD_CLIENTE FROM CLIENTE WHERE USERNAME = '" + username + "'), CURRENT_TIMESTAMP, 'Dados de cliente actualizados')";
            command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO ACTUALIZAR LOGFILE: " + e);
            }
            con.Close();
        }

        public void actualizarPontosCliente(String username)
        {
            string query = "UPDATE CLIENTE SET PONTOS_TOTAIS = (PONTOS_TOTAIS+1000) WHERE USERNAME = '" + username + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO ACTUALIZAR PONTOS CLIENTE: " + e);
            }
            con.Close();

            query = "INSERT INTO LOGFILE (COD_CLIENTE,DATA,DESCRICAO) VALUES ((SELECT COD_CLIENTE FROM CLIENTE WHERE USERNAME = '" + username + "'), CURRENT_TIMESTAMP, 'Pontos adicionados por codigo inserido')";
            command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO ACTUALIZAR LOGFILE: " + e);
            }
            con.Close();
        }

        public int obterPontosDisponiveis(String username)
        {
            string query = "SELECT (PONTOS_TOTAIS-PONTOS_GASTOS) FROM CLIENTE WHERE USERNAME = '" + username + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            int pontos = -1;
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                    pontos = reader.GetInt32(0);
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO OBTER PONTOS CLIENTE:" + e);
            }
            con.Close();
            return pontos;
        }

        public bool verificarPontosSuficientes(String username, int pontos_nec)
        {
            string query = "SELECT (PONTOS_TOTAIS - PONTOS_GASTOS) FROM CLIENTE WHERE USERNAME = '" + username + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            int pontos_cli = 0;
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                    pontos_cli = reader.GetInt32(0);
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO OBTER PONTOS CLIENTE:" + e);
            }
            con.Close();
            if (pontos_cli - pontos_nec >= 0) return true;
            else return false;
        }

        public void retirarClientePontos(String username, int pontos, String premio)
        {
            string query = "UPDATE CLIENTE SET PONTOS_GASTOS = (PONTOS_GASTOS + " + pontos + ") WHERE USERNAME = '" + username + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO RETIRAR PONTOS CLIENTE: " + e);
            }
            con.Close();

            query = "INSERT INTO CLIENTE_PREMIO (COD_PREMIO,COD_CLIENTE) VALUES ((SELECT COD_PREMIO FROM PREMIO WHERE NOME_PREMIO = '" + premio + "'),(SELECT COD_CLIENTE FROM CLIENTE WHERE USERNAME = '" + username + "'))";
            command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO INDICAR PREMIO ADQUIRIDO PELO CLIENTE: " + e);
            }
            con.Close();

            query = "INSERT INTO LOGFILE (COD_CLIENTE,DATA,DESCRICAO) VALUES ((SELECT COD_CLIENTE FROM CLIENTE WHERE USERNAME = '" + username + "'), CURRENT_TIMESTAMP, 'Premio associado ao cliente e pontos retirados')";
            command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO ACTUALIZAR LOGFILE: " + e);
            }
            con.Close();

        }

        public bool existeVotoClienteFilme(String username, String filme, String ano)
        {
            string query = "SELECT VOTO FROM CLIENTE INNER JOIN VOTO_CLIENTE_FILME ON CLIENTE.COD_CLIENTE = VOTO_CLIENTE_FILME.COD_CLIENTE INNER JOIN FILME ON VOTO_CLIENTE_FILME.COD_FILME = FILME.COD_FILME WHERE USERNAME = '" + username + "' AND NOME_FILME = '" + filme + "' AND YEAR(DATA_ESTREIA) = " + ano;
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            bool existe = reader.HasRows;
            con.Close();
            return existe;
        }

        public void novoVoto(String username, String filme, String ano, int voto)
        {
            string query = "SELECT COD_CLIENTE FROM CLIENTE WHERE USERNAME = '" + username + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            int cod_cliente = 0;
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                    cod_cliente = reader.GetInt32(0);
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO OBTER CODIGO CLIENTE:" + e);
            }
            con.Close();

            query = "SELECT COD_FILME FROM FILME WHERE NOME_FILME = '" + filme + "' AND YEAR(DATA_ESTREIA) = " + ano;
            command = new OdbcCommand(query, con);
            int cod_filme = 0;
            con.Open();
            reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                    cod_filme = reader.GetInt32(0);
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO OBTER CODIGO FILME:" + e);
            }
            con.Close();


            query = "INSERT INTO VOTO_CLIENTE_FILME (COD_CLIENTE,COD_FILME,VOTO) VALUES (" + cod_cliente + ", " + cod_filme + ", " + voto + ")";
            command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO INSERIR VOTO: " + e);
            }
            con.Close();

            query = "INSERT INTO LOGFILE (COD_CLIENTE,DATA,DESCRICAO) VALUES (" + cod_cliente + ", CURRENT_TIMESTAMP, 'Classificacao do filme " + cod_filme + "')";
            command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO ACTUALIZAR LOGFILE: " + e);
            }
            con.Close();

        }

        public void alterarVoto(String username, String filme, String ano, int voto)
        {
            string query = "SELECT COD_CLIENTE FROM CLIENTE WHERE USERNAME = '" + username + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            int cod_cliente = 0;
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                    cod_cliente = reader.GetInt32(0);
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO OBTER CODIGO CLIENTE:" + e);
            }
            con.Close();

            query = "SELECT COD_FILME FROM FILME WHERE NOME_FILME = '" + filme + "' AND YEAR(DATA_ESTREIA) = " + ano;
            command = new OdbcCommand(query, con);
            int cod_filme = 0;
            con.Open();
            reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                    cod_filme = reader.GetInt32(0);
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO OBTER CODIGO FILME:" + e);
            }
            con.Close();


            query = "UPDATE VOTO_CLIENTE_FILME SET VOTO = " + voto + " WHERE COD_CLIENTE = " + cod_cliente + " AND COD_FILME = " + cod_filme;
            command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO ALTERAR VOTO: " + e);
            }
            con.Close();

            query = "INSERT INTO LOGFILE (COD_CLIENTE,DATA,DESCRICAO) VALUES (" + cod_cliente + ", CURRENT_TIMESTAMP, 'Alteracao da Classificacao do filme " + cod_filme + "')";
            command = new OdbcCommand(query, con);
            con.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO ACTUALIZAR LOGFILE: " + e);
            }
            con.Close();
        }

        public void obterCoordenadas(String username, Clientes c)
        {
            string query = "SELECT LATITUDE, LONGITUDE FROM CLIENTE WHERE USERNAME = '" + username + "'";
            OdbcCommand command = new OdbcCommand(query, con);
            con.Open();
            OdbcDataReader reader = command.ExecuteReader();
            try
            {
                while (reader.Read())
                {
                    c.latitude = reader.GetDouble(0);
                    c.longitude = reader.GetDouble(1);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("ERRO AO OBTER COORDENADAS CLIENTE:" + e);
            }
            con.Close();
        }

    }
}